package com.aiflow.util;

import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.aiflow.db.ConnDB;

public class DownExcell {
     //下载 主方法 
	public void getData(String sql, HttpServletRequest req,
		HttpServletResponse res, String fileName) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		try {
			ConnDB db = new ConnDB();
			Connection con = db.getConnection();
			PreparedStatement ps = null;
			ResultSet set = null;
			ps = con.prepareStatement(sql);
			set = ps.executeQuery();
			query(set, sheet); // 生成 excel
			down(workbook, res, fileName); // 下载
		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}
	}

	 //下载
	public void down(HSSFWorkbook workbook, HttpServletResponse response,
			String name) {
		try {
			name = new String(name.getBytes(), "ISO8859-1");
		} catch (UnsupportedEncodingException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		response.reset();
		response.setContentType("application/msexcel;charset=UTF-8");
		try {
			response.setHeader("Content-Disposition", "attachment;filename=\""
					+ name + "\".xls");
			OutputStream out = response.getOutputStream();
			workbook.write(out);
			out.flush();
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 查询 数据到 excel
	public void query(ResultSet set, HSSFSheet sheet) {
		HSSFRow row = null;
		HSSFCell cell = null;
		List listType = new ArrayList();
		int datatype = -1;
		int rowCount = 0;
		ResultSetMetaData meta;
		try {
			meta = set.getMetaData();
			int count = meta.getColumnCount();
			String colName = "";
			row = sheet.createRow(rowCount);
			cell = null;
			for (int i = 1, k = 0; i <= count; i++, k++) {
				colName = meta.getColumnName(i);
				datatype = meta.getColumnType(i);
				cell = row.createCell(k);
				cell.setCellValue(colName);
				listType.add(datatype);
			}
			String val = null;
			while (set.next()) {
				row = sheet.createRow(++rowCount);
				for (int i = 1, j = 0; i <= count; i++, j++) {
					cell = row.createCell(j);
					int jj = (Integer) listType.get(j);
					switch (jj) {
					case 4:
						cell.setCellValue(set.getInt(i));
						break;
					case 12:
						cell.setCellValue(set.getString(i));
						break;
					default:
						cell.setCellValue(set.getString(i));
						break;
					}
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void getObject(List list, int typeId) {
		switch (typeId) {
		case 4:
			break;
		case 12:
			break;
		default:
			break;
		}

	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}
